#设置各种参数
# typeset ORA_PRI_IP='@@ORA_PRI_IP@@'               #主服务器ip 10.27.5.199
# typeset ORA_SLA_IP='@@ORA_SLA_IP@@'               #从服务器ip 10.27.5.139
# typeset ORA_PRI_TNS_PORT='@@ORA_PRI_TNS_PORT@@' #1521
# typeset ORA_SLA_TNS_PORT='@@ORA_SLA_TNS_PORT@@' #1521
# typeset ORA_SLA_SSH_PORT='@@ORA_SLA_SSH_PORT@@' #22
# typeset ORA_PRI_HOST_USER='@@ORA_PRI_HOST_USER@@'                   #主服务器远程用户 oracle
# typeset ORA_PRI_HOST_PASSWD='@@ORA_PRI_HOST_PASSWD@@'           #主服务器远程密码 oracle
# typeset ORA_SLA_HOST_USER='@@ORA_SLA_HOST_USER@@'                   #从服务器远程用户 oracle
# typeset ORA_SLA_HOST_PASSWD='@@ORA_SLA_HOST_PASSWD@@'           #从服务器远程密码 oracle
# typeset check_times='@@check_times@@'                  #命令重复次数
# typeset ORA_PRI_SID='@@ORA_PRI_SID@@'                       #设置主库SID orcl
# typeset ORA_PRI_SID_UP=$(echo "${ORA_PRI_SID}" | tr '[a-z]' '[A-Z]') #ORCL
# typeset ORA_PRI_SID_DOWN=$(echo "${ORA_PRI_SID}" | tr '[A-Z]' '[a-z]') #ORCL
# typeset ORA_SLA_SID='@@ORA_SLA_SID@@'                       #设置从库SID orcl
# typeset ORA_PRI_DB_UNIQUE_NAME='@@ORA_PRI_DB_UNIQUE_NAME@@'            #设置主库uniqname orcl_pri
# typeset ORA_SLA_DB_UNIQUE_NAME='@@ORA_SLA_DB_UNIQUE_NAME@@'            #设置从库uniqname orcl_stl
# typeset ORA_PRI_SERVICE_NAME='@@ORA_PRI_SERVICE_NAME@@' #fsda.pri orcl_pri.pri
# typeset ORA_SLA_SERVICE_NAME='@@ORA_SLA_SERVICE_NAME@@' #orcl_stl.stl orcl_stl.pri
#ORACLE_OWNER=oracle
#ORACLE_SID=orcl
#ORACLE_HOSTNAME=pri
#ORACLE_BASE=/db/app/oracle
#ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/db_1
#SPFILE_PATH=${ORACLE_HOME}/dbs
#ORA_NLS10=${ORACLE_HOME}/nls/data
#TNS_ADMIN=${ORACLE_HOME}/network/admin
# typeset ORA_PRI_SID_UP=$(echo "${ORA_PRI_SID}" | tr '[a-z]' '[A-Z]') #ORCL
# typeset ORA_PRI_SID_DOWN=$(echo "${ORA_PRI_SID}" | tr '[A-Z]' '[a-z]') #ORCL

### test begin ###
typeset ORA_PRI_IP='10.27.5.199'               #主服务器ip
typeset ORA_SLA_IP='10.27.5.139'               #从服务器ip
typeset ORA_PRI_TNS_PORT='1521'
typeset ORA_SLA_TNS_PORT='1521'
typeset ORA_SLA_SSH_PORT='22' #22
typeset ORA_SLA_HOST_USER='oracle'                   #从服务器远程用户
typeset ORA_SLA_HOST_PASSWD='oracle'           #从服务器远程密码
typeset check_times='5'                  #命令重复次数
typeset ORA_PRI_SID='orcl'                       #设置主库SID orcl
typeset ORA_SLA_SID='orcl'                       #设置从库SID orcl
typeset ORA_PRI_DB_UNIQUE_NAME='orcl_pri'            #设置主库uniqname orcl_pri
typeset ORA_SLA_DB_UNIQUE_NAME='orcl_stl'            #设置从库uniqname orcl_stl
typeset ORA_PRI_SERVICE_NAME='orcl_pri.pri' #fsda.pri orcl_pri.pri
typeset ORA_SLA_SERVICE_NAME='orcl_stl.pri' #orcl_stl.stl orcl_stl.pri
### test end ###

#配置日志路径
typeset SCRIPT_NAME="oracle_master_$ORA_PRI_IP"
typeset LOG_PATH='/tmp/dr_log'
[ ! -d "${LOG_PATH}" ] && mkdir -p ${LOG_PATH}
typeset LOG_FILE="${LOG_PATH}/${SCRIPT_NAME}.log"
[ ! -f "${LOG_FILE}" ] && touch ${LOG_FILE}

function log_format()
{
    echo "[`date +"%F %H:%M:%S"`] [$*]" | tee -a ${LOG_FILE}
}

log_format "begin to build oracle master................................."

#设置oracle环境变量
source ~/.bash_profile

#检查check次数
[ ! ${check_times} ] && typeset check_times='3'
expr 1 + ${check_times} &>/dev/null
if [ $? -ne 0 ];then
    log_format "ERROR" 'Argument needs to be integer';exit 1
fi

#获取oracle数据库状态
status=$(sqlplus / as sysdba <<eof |grep -w OPEN| sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g'
select status from v\$instance;
eof
)
if [ "$status" != OPEN ];then
    log_format "ERROR" 'oracle database is not open !' && exit 1
else
    log_format "INFO" 'oracle database is open,continue...'
fi

#获取archive mode是否开启
mode=`echo 'archive log list;'|sqlplus / as sysdba|grep 'Archive Mode'|awk -F'\t' '{print $2}'|sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g'`
echo $mode
if [ "$mode" != "Archive Mode" ];then
    log_format "WARNING" 'archive mode is not open'
    log_format "INFO" 'try to start archive mode'
    sqlplus / as sysdba <<eof | tee -a ${LOG_FILE}
shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
eof
    if [ $? -ne 0 ];then
        log_format "ERROR" "sql command failed!" && exit 1
    fi
    mode=`echo 'archive log list;'|sqlplus / as sysdba|grep 'Archive Mode'|awk -F'\t' '{print $2}'|sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g'`
    if [ "$mode" != "Archive Mode" ];then
        log_format "ERROR" 'fail to open archive mode' && exit 1
    else
        log_format "INFO" 'success to open archive mode'
    fi
else
    log_format "INFO" "archive mode is open"
fi

#创建standby redo log
log_format "INFO" "start to create standby redo log..."
##获取standby日志数量 参数可以根据实际情况调整，避免换行带来的数据错乱
sqlplus / as sysdba <<eof  > /tmp/vlogfile.txt
set linesize 300;
set pagesize 300;
column member format a70
select group#,member,type from v\$logfile;
eof
cat /tmp/vlogfile.txt | tee -a ${LOG_FILE}

##获取standby日志大小
sqlplus / as sysdba <<eof > /tmp/onlinelogsize.txt
select group#,bytes/1024/1024 as size_m from v\$log;
eof
cat /tmp/onlinelogsize.txt | tee -a ${LOG_FILE}


standbynum=`cat /tmp/vlogfile.txt|grep STANDBY|grep -v grep|wc -l`
onlinenum=`cat /tmp/vlogfile.txt|grep ONLINE|grep -v grep|wc -l`
if [ $standby -ne 0 ];then
    log_format "ERROR" "standbylog already exits,please check!" && exit 1
fi

maxsize=0
maxgroup=0
for i in `cat /tmp/onlinelogsize.txt |grep -A $(($onlinenum+1)) "SIZE_M"|tail -$onlinenum|awk '{print $2}'|sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g'`
do
    if [ $i -gt "$maxsize" ];then
        maxsize=$i
    fi
done

for i in `cat /tmp/onlinelogsize.txt |grep -A $(($onlinenum+1)) "SIZE_M"|tail -$onlinenum|awk '{print $1}'|sed -e 's/^[ ]*//g' | sed -e 's/[ ]*$//g'`
do
    if [ $i -gt "$maxgroup" ];then
        maxgroup=$i
    fi
done
echo $maxsize
echo $onlinenum
echo $maxgroup

##创建redolog
for ((i=1;i<=$(($onlinenum+1));i++))
do
let redonum=maxgroup+i
sqlplus / as sysdba <<eof | tee -a ${LOG_FILE}
ALTER DATABASE ADD STANDBY LOGFILE GROUP $redonum ('$ORACLE_BASE/oradata/${ORA_PRI_SID}/redo0${redonum}.log') size ${maxsize}M;
eof
done
if [ $? -ne 0 ];then
    log_format "ERROR" "failed to add standby log " && exit 1
else
    log_format "INFO" "success to run add standby log "
fi

#检查添加的日志组
log_format "INFO" "start to check standby log "
sqlplus / as sysdba <<eof > /tmp/stlogstat.txt
select group#,sequence#,bytes,status from v\$standby_log;
eof
cat /tmp/stlogstat.txt | tee -a ${LOG_FILE}
for ((i=1;i<=$(($onlinenum+1));i++))
do
let redonum=maxgroup+i
tmpnum=`cat /tmp/stlogstat.txt |grep -A $(($onlinenum+2)) STATUS|tail -$(($onlinenum+1))|awk '{print $1}'|grep -w $redonum|grep -v grep|wc -l`
if [ $tmpnum -ne 1 ];then
    log_format "ERROR" "redolog group $redonum add failed!" && exit 1
else
    log_format "INFO" "find redolog group $redonum"
fi
done

#生成pfile文件并修改
sqlplus / as sysdba << EOFAA
create pfile from spfile;
EOFAA

cd $ORACLE_HOME/dbs

if [ ! -d "${ORACLE_BASE}/oradata/${ORA_PRI_SID}/archivelog" ]
then
  mkdir ${ORACLE_BASE}/oradata/${ORA_PRI_SID}/archivelog
  if [ $? -ne 0 ]
  then
    log_format "ERROR" "create dir ${ORACLE_BASE}/oradata/${ORA_PRI_SID}/archivelog failed."
    exit 10
  fi
fi

echo "DB_UNIQUE_NAME=${ORA_PRI_DB_UNIQUE_NAME}
LOG_ARCHIVE_CONFIG='DG_CONFIG=(${ORA_PRI_DB_UNIQUE_NAME},${ORA_SLA_DB_UNIQUE_NAME})'
LOG_ARCHIVE_DEST_1=
 'LOCATION=${ORACLE_BASE}/oradata/${ORA_PRI_SID}/archivelog
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=${ORA_PRI_DB_UNIQUE_NAME}'
LOG_ARCHIVE_DEST_2=
 'SERVICE=${ORA_SLA_DB_UNIQUE_NAME} ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=${ORA_SLA_DB_UNIQUE_NAME}'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
## Paraneters which using for switch over from Primary to Standby.
FAL_SERVER=${ORA_SLA_DB_UNIQUE_NAME}
STANDBY_FILE_MANAGEMENT=AUTO
db_file_name_convert='${ORA_PRI_SID}','${ORA_PRI_SID}'
log_file_name_convert='${ORA_PRI_SID}','${ORA_PRI_SID}'" >> init${ORA_PRI_SID}.ora

sqlplus / as sysdba << EOFAA
shutdown immediate;
create spfile from pfile;
startup;
EOFAA

cd $ORACLE_HOME/network/admin

# ORA_PRI_DB_UNIQUE_NAME 可能大小写
# 主库监听配置
typeset pri_line_lst=($(grep -ni "^${ORA_PRI_DB_UNIQUE_NAME} " -A8 tnsnames.ora | grep -E 'HOST|SERVICE_NAME' | sed 's/-//g' | awk '{print $1}'))
if [ -z "${pri_line_lst}" ]
then
  echo "${ORA_PRI_DB_UNIQUE_NAME} =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ${ORA_PRI_IP})(PORT = ${ORA_PRI_TNS_PORT}))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ${ORA_PRI_SERVICE_NAME})
    )
  )

" >> tnsnames.ora
else
  sed -i "${pri_line_lst[0]}s/HOST =.*/HOST = ${ORA_PRI_IP})(PORT = ${ORA_PRI_TNS_PORT}))/g" tnsnames.ora
  sed -i "${pri_line_lst[1]}s/SERVICE_NAME =.*/SERVICE_NAME = ${ORA_PRI_SERVICE_NAME})/g" tnsnames.ora
fi

# 默认配置
typeset sid_line_lst=($(grep -ni "^${ORA_PRI_SID} " -A8 tnsnames.ora | grep -E 'HOST|SERVICE_NAME' | sed 's/-//g' | awk '{print $1}'))
if [ -z "${sid_line_lst}" ]
then
  log_format "ERROR" "tnsnames.ora ERROR!"
  exit 10
fi
sed -i "${sid_line_lst[0]}s/HOST =.*/HOST = localhost)(PORT = ${ORA_PRI_TNS_PORT}))/g" tnsnames.ora
sed -i "${sid_line_lst[1]}s/SERVICE_NAME =.*/SERVICE_NAME = ${ORA_PRI_SERVICE_NAME})/g" tnsnames.ora

# 备库监听配置
typeset sla_line_lst=($(grep -ni "^${ORA_SLA_DB_UNIQUE_NAME} " -A8 tnsnames.ora | grep -E 'HOST|SERVICE_NAME|(UR=A)' | sed 's/-//g' | awk '{print $1}'))
if [ -z "${sla_line_lst}" ]
then
  echo "${ORA_SLA_DB_UNIQUE_NAME} =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ${ORA_SLA_IP})(PORT = ${ORA_SLA_TNS_PORT}))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ${ORA_SLA_SERVICE_NAME})
      (UR=A)
    )
  )

" >> tnsnames.ora
else  
  sed -i "${sla_line_lst[0]}s/HOST =.*/HOST = ${ORA_SLA_IP})(PORT = ${ORA_SLA_TNS_PORT}))/g" tnsnames.ora
  sed -i "${sla_line_lst[1]}s/SERVICE_NAME =.*/SERVICE_NAME = ${ORA_SLA_SERVICE_NAME})/g" tnsnames.ora
  if [ ${#sla_line_lst[*]} -eq 2 ]
  then
    sed -i "${sla_line_lst[1]}a      (UR=A)" tnsnames.ora
  fi
fi


#拷贝文件
cd $ORACLE_HOME/dbs

if which expect
then
  log_format "INFO" "expect OK!"
else
  log_format "ERROR" "expect NOT found!"
  exit 10
fi

#拷贝密码文件
expect << EOFXX
spawn scp -rp -P${ORA_SLA_SSH_PORT} orapw${ORA_PRI_SID} ${ORA_SLA_HOST_USER}@${ORA_SLA_IP}:$ORACLE_HOME/dbs/orapw${ORA_SLA_SID}

expect {
	"*password:*" {
		set timeout 1000
		send "${ORA_SLA_HOST_PASSWD}\r"
		exp_continue
		}
	"*es/no)?*" {
		send "yes\r"
		exp_continue
		}
	timeout {
		puts "connect is timeout"
		exit 3
		}
	}
EOFXX

#拷贝pfile参数文件
expect << EOFYY
spawn scp -rp -P${ORA_SLA_SSH_PORT} init${ORA_PRI_SID}.ora ${ORA_SLA_HOST_USER}@${ORA_SLA_IP}:$ORACLE_HOME/dbs/init${ORA_SLA_SID}.ora

expect {
	"*password:*" {
		set timeout 1000
		send "${ORA_SLA_HOST_PASSWD}\r"
		exp_continue
		}
	"*es/no)?*" {
		send "yes\r"
		exp_continue
		}
	timeout {
		puts "connect is timeout"
		exit 3
		}
	}
EOFYY

#拷贝tnsnames配置文件
expect << EOFZZ
spawn scp -rp -P${ORA_SLA_SSH_PORT} $ORACLE_HOME/network/admin/tnsnames.ora ${ORA_SLA_HOST_USER}@${ORA_SLA_IP}:$ORACLE_HOME/network/admin/tnsnames.ora

expect {
	"*password:*" {
		set timeout 1000
		send "${ORA_SLA_HOST_PASSWD}\r"
		exp_continue
		}
	"*es/no)?*" {
		send "yes\r"
		exp_continue
		}
	timeout {
		puts "connect is timeout"
		exit 3
		}
	}
EOFZZ

lsnrctl stop
if [ $? -ne 0 ];then
    log_format "ERROR" "lsnrctl stop failed." && exit 1
else
    log_format "INFO" "lsnrctl stop successfully."
fi

lsnrctl start
if [ $? -ne 0 ];then
    log_format "ERROR" "lsnrctl start failed." && exit 1
else
    log_format "INFO" "lsnrctl start successfully."
fi

MAIN_RET=$?
log_format "INFO" "MAIN_RET=${MAIN_RET}"

if [[ "$MAIN_RET" == 0 ]]
then
    log_format "INFO" "script_execute_success!"
else
    log_format "ERROR" "script_execute_error!"
fi

exit $MAIN_RET